using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Serialization;

using WebTools.Data;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public static class DataAccess
{
	public static List<Product> GetProducts()
	{
		// Create the XQuery object instance based on the webtools_Objects schema.
		XQuery query = new XQuery(Schemas.ObjectsSchema);

		// Add a normal column where clause.
		query.AddWhere("TypeName", typeof(Product).FullName);

		List<Product> list = new List<Product>();
		using (SqlNullDataReader reader = SqlHelper.ExecuteReader(DataAccess.ConnectionString, query))
		{
			while (reader.Read())
			{
				// Using the SqlXml type to retrieve the xml data from the column.
				SqlXml xml = reader.GetSqlXml("ObjectXml");

				// The type name is stored in the table for easy deserialization.
				Type t = Type.GetType(reader.GetString("TypeName"));

				// Deserialize the object using the XmlReader created on the xml data type column.
				Product obj = DataAccess.Deserialize(t, xml.CreateReader()) as Product;

				if (obj != null)
				{
					// Setting the ID of the product to the database ObjectId.
					obj.ID = reader.GetInt32("ObjectId");
					list.Add(obj);
				}
			}
		}
		return list;
	}

	public static Product GetProduct(string name)
	{
		// Create the XQuery object instance based on the webtools_Objects schema.
		XQuery query = new XQuery(Schemas.ObjectsSchema);

		// Add a where clause to search the xml data type for a name that matches the specified name.
		query.AddXWhere("ObjectXml", "Name", name);

		using (SqlNullDataReader reader = SqlHelper.ExecuteReader(DataAccess.ConnectionString, query))
		{
			if (reader.Read())
			{
				// Using the SqlXml type to retrieve the xml data from the column.
				SqlXml xml = reader.GetSqlXml("ObjectXml");

				// The type name is stored in the table for easy deserialization.
				Type t = Type.GetType(reader.GetString("TypeName"));

				// Deserialize the object using the XmlReader created on the xml data type column.
				Product obj = DataAccess.Deserialize(t, xml.CreateReader()) as Product;

				if (obj != null)
				{
					// Setting the ID of the product to the database ObjectId.
					obj.ID = reader.GetInt32("ObjectId");
				}
				return obj;
			}
		}
		return null;
	}

	public static bool ProductExists(string name)
	{
		return (DataAccess.GetProduct(name) != null);
	}

	public static void SaveProduct(Product product)
	{
		// Deserialize the object into an XML string.
		string xml = DataAccess.Serialize(product);

		string cmdText = String.Empty;
		List<SqlParameter> sqlParams = new List<SqlParameter>();

		Product obj = DataAccess.GetProduct(product.Name);
		if (obj != null)
		{
			// Update the databse.
			cmdText = "UPDATE webtools_Objects SET ObjectXml=@ObjectXml WHERE ObjectId=@ObjectId";
			sqlParams.Add(SqlHelper.CreateInputParam("@ObjectId", SqlDbType.Int, obj.ID));
			sqlParams.Add(SqlHelper.CreateInputParam("@ObjectXml", SqlDbType.Xml, xml));
		}
		else
		{
			// Insert into the database.
			cmdText = "INSERT INTO webtools_Objects (TypeName, ObjectXml) VALUES (@TypeName, @ObjectXml)";
			sqlParams.Add(SqlHelper.CreateInputParam("@TypeName", SqlDbType.NVarChar, typeof(Product).FullName));
			sqlParams.Add(SqlHelper.CreateInputParam("@ObjectXml", SqlDbType.Xml, xml));
		}

		SqlHelper.ExecuteNonQuery(DataAccess.ConnectionString, cmdText, CommandType.Text, sqlParams.ToArray());
	}

	#region Serlialize/Deserialize
	public static string Serialize(object obj)
	{
		string xml = null;
		using (MemoryStream ms = new MemoryStream())
		{
			XmlSerializer serializer = new XmlSerializer(obj.GetType());
			serializer.Serialize(ms, obj);

			ms.Seek(0, SeekOrigin.Begin);
			using (StreamReader sr = new StreamReader(ms))
			{
				xml = sr.ReadToEnd();
			}
			ms.Close();
		}
		return xml;
	}
	public static object Deserialize(Type objectType, XmlReader reader)
	{
		XmlSerializer serializer = new XmlSerializer(objectType);
		return serializer.Deserialize(reader);
	}
	#endregion

	public static string ConnectionString
	{
		get { return ConfigurationManager.ConnectionStrings["WebToolsConnection"].ConnectionString; }
	}
}
